var fs=require('fs');
var PostgreSQL=require('pgsql').PostgreSQL;

/**
 * Class: LNSql
 *	An SQL connection class
 *
 */

/**
 * Method: Constructor
 *
 * Parameters:
 *		config typeof DatabaseConfig - Configuration for database connection
 *		paths typeof Array - A list of paths to search for .sql files
 */
LNSql=function(config,paths) {
	this.paths=paths;
	this.prepared_query={};
	this.prepared_array={};
	this.fallback_href1={};
	this.fallback_href2={};
	this.config=config;
	this.connect();
	this.was_connected=1;
	this.logs={};
	this.cnt_commits=0;
	return this;
}

/**
 * Method: set_fallback_href
 *		Sets fallback href
 *
 * Parameters:
 * 		href1 typeof Object - Fallback href
 * 		href2 typeof Object - Fallback href
 */
LNSql.prototype.set_fallback_href=function(href1,href2) {
	this.fallback_href1=href1;
	this.fallback_href2=href2;
}

/**
 * Method: load
 *		Loads file, searching in pre-set paths
 *
 * Parameters:
 *		filename typeof String - File name, without .sql extension
 */
LNSql.prototype.load=function(filename) {
	var i=0;
	var pathes="";
	while (i<this.paths.length) {
		var path=this.paths[i]+filename+".sql";
		try {
			var f=new fs.File(path);
			f.open('r');
			var contents=f.read();
			f.close();
			return contents.toString("utf-8");
		} catch(e) {
			if (pathes.length) pathes+=", ";
			pathes+=path;
			i++;
			continue;
		}
	}
	throw new Error("SQL:load() - File not found - "+filename+"\nSQL: searched "+pathes+"\n");
}

/**
 * Method: connect
 *		Connects to database
 *
 */
LNSql.prototype.connect=function() {
	var database=this.config;
	var t=this;
	this.autocommit=database.autocommmit||false;
	switch (database.driver||'pg') {
		case 'pg':
			var url="";
			url+="host="+(database.host||'127.0.0.1');
			url+=" port="+(database.port||5432);
			url+=" dbname="+database.database;
			url+=" user="+database.login;
			url+=" password="+database.password;
			this.dbh=new PostgreSQL(url);
			this.dbh.queryParams("set client_min_messages=ERROR",[]);
			break;
		default:
			throw new Error("Unknown database driver "+database.driver);
	}
}

/**
 * Method: auto_start_transaction
 *		Starts transaction if it was not started and autocommit is off
 *
 */
LNSql.prototype.auto_start_transaction=function()
{
	if (this.tr_started || this.autocommit) return;
	this.start_transaction();
}

/**
 * Method: start_transaction
 *		Starts transactions
 *
 */
LNSql.prototype.start_transaction=function()
{
	if (this.tr_started) return;
	try {
		if (this.config.on_query) this.config.on_query(this,"begin /* ======================= */");
		var res=this.dbh.queryParams("begin",[]);
		res.clear();
		this.tr_started=true;
//		throw new Error("SQL:start_transaction() - TEMP");
	} catch (e) {
		throw new Error("SQL:start_transaction() - "+e);
	}
}

/**
 * Method: prepare
 *		Prepares an SQL statement, parsing :placeholders.
 *
 * Parameters:
 * 		text typeof String - SQL query
 *		prsql typeof String - Name to store prepared statement as. Either a file name or SQL query text itsel.
 */
LNSql.prototype.prepare=function(text,prsql) {
	var arr=[];
	var hash={};
	var a;
	var i=0;
	var src=text;
	while (a=text.match(/([^:\w]):([a-z_0-9]+)/)) {
		var pre=a[1];
		var varname=a[2];
		var pos;
		if (hash[varname]) {
			pos=hash[varname];
		} else {
			arr.push(varname);
			pos=arr.length;
			hash[varname]=pos;
		}
		text=text.replace(/[^:\w]:[a-z_0-9]+/,pre+"$"+pos);
	}
//	this.prepared_query[prsql]=this.dbh.prepare(filename,text);
	this.prepared_query[prsql]=text;
	this.prepared_array[prsql]=arr;
}

/**
 * Method: last_insert_id
 *		Returns last inserted id for a table
 *
 * Parameters:
 *		tablename typeof String - database table name
 * Returns:
 *		typeof Integer - last inserted id
 */
LNSql.prototype.last_insert_id=function(tablename) {
	return this.execute_helper("select currval(pg_get_serial_sequence(:table,'id')) as lastid",{table: tablename.toLowerCase()},1,1).lastid;
}

/**
 * Method: max_id
 *		Returns max id for a table
 *
 * Parameters:
 *		tablename typeof String - database table name
 * Returns:
 *		typeof Integer - maximum id
 */
LNSql.prototype.max_id=function(tablename) {
	return this.execute_helper("select max(id) as maxid from " + tablename.toLowerCase(),{},1,1).maxid;
}

/**
 * Method: bind
 *		Binds data in href to a query.
 *		Since PostgreSQL cannot use :placeholder syntax but only $number syntax, a binding takes places
 *		Order or search is href[placeholder], href2[placeholder], this.fallback_href1[placeholder], this.fallback_href2[placeholder]
 *
 * Parameters:
 *		prsql typeof String -  prepared sql statement name
 *		href typeof Object -  Main href
 *		href2 typeof Object - Secondary href
 */
LNSql.prototype.bind=function(prsql,href,href2) {
	try {
		var arr=[];
		var parr=this.prepared_array[prsql];
		var href3=this.fallback_href1;
		var href4=this.fallback_href2;
		for (var i=0;i<parr.length;i++) {
			var pholder=parr[i];
			var value=undefined;
			if (href && pholder in href) {
				value=href[pholder];
			} else if (href2 && pholder in href2) {
				value=href2[pholder];
			} else if (href3 && pholder in href3) {
				value=href3[pholder];
			} else if (href4 && pholder in href4) {
				value=href4[pholder];
			} 
			arr.push(value);
		}
		return arr;
	} catch (e) {
		this.rollback();
		throw new Error("SQL:bind("+prsql+"): "+e);
	}
}

/**
 * Method: rollback
 *		Issues database rollback 
 *
 */
LNSql.prototype.rollback=function()
{
	if (!this.tr_started) return;
	try {
		if (this.config.on_query) this.config.on_query(this,"rollback /* ======================= */");
		var res=this.dbh.queryParams("rollback",[]);
		res.clear();
		this.tr_started=false;
	} catch (e) {
		//throw new Error("SQL:rollback - "+e);
	}
}

/**
 * Method: commit
 * Issues database commit
 *
 */
LNSql.prototype.commit=function()
{
	if (!this.tr_started) return;
	this.cnt_commits++;
//	if (this.cnt_commits==2) throw new Error("TMP COMMIT");
	try {
		if (this.config.on_query) this.config.on_query(this,"commit /* ======================= */");
		var res=this.dbh.queryParams("commit",[]);
		res.clear();
		this.tr_started=false;
	} catch (e) {
		throw new Error("SQL:commit - "+e);
	}
}

/**
 * Method: execute_helper
 *		Helper method to execute database queries 
 *
 * Parameters:
 *		q typeof String				- SQL file or sql query text, depending on is_single
 *		href typeof Object			- href Arguments to bind
 *		is_single typeof Boolean 	- Flag, if q is sql query (true) or file (false) 
 * 		cnt typeof integer			- cnt Count of lines to fetch, possible values: 0, 1, -1
 *		href2 typeof Object			- Extra arguments to bind
 *
 * Returns:
 * 		typeof ? - Array of objects (cnt!=1) or Object or null (if cnt==1)
 */
LNSql.prototype.execute_helper=function(q,href,is_single,cnt,href2)
{
	if (!this.dbh.isConnected() && this.was_connected) this.connect(this.config);
	try {
		var d1=new Date();
		if (!this.prepared_query[q]) this.prepare(is_single?q:this.load(q),q);
		var values=this.bind(q,href,href2);
		var sqlq=this.prepared_query[q];
//		if (href && href!=this.fallback_href && href!=this.fallback_href) {
//			if (href.REPLACE) {
//				for (var k in href.REPLACE) sqlq=sqlq.replace("/*"+k+"*/",href.REPLACE[k]);
//			}
//			if (href.FOOTER) sqlq+=href.FOOTER;
//		}
//		if (href && href.BIND_ADD) {
//			// TODO forgot how to add array to array
//			href.BIND_ADD.forEach(function(x) {values.push(x);});
//		}
		this.auto_start_transaction();
		if (this.config.on_query) this.config.on_query(this,sqlq);
		var res=this.dbh.queryParams(sqlq,values);
		var ret=undefined;
		if (cnt==0) {
		} else {
			ret=res.fetchAllObjects();
			if (cnt==1 && ret) ret=ret[0];
		}
		if (this.log_enabled) {
			var d2=new Date();
			var diff=d2.getTime()-d1.getTime();
			if (!this.logs[q]) {
				var a=this.logs[q]={cnt:1,time:diff/1000,ids:[]};
				if (q=="_models/file_folders/get") a.ids.push(href.id);
			} else {
				var a=this.logs[q];
				a.cnt++;
				a.time+=diff/1000;
				if (q=="_models/file_folders/get") a.ids.push(href.id);
			}
		}
		res.clear();
		return ret;
	} catch(e) {
		if (res) res.clear();
		this.rollback();
		throw new Error("SQL:execute_helper("+q+") - "+e+",\nbind='"+(values?values.join("','"):"")+"'\nquery:\n"+sqlq+"\n");
	}
}

/**
 * Method: execute
 *		Executes an SQL file ( used for INSERT/UPDATE/DELETE)
 *
 * Parameters:
 *		file typeof string	- file File name without .sql extension
 *		href typeof Object	- href Bind arguments
 *		href2 typeof Object	- Fallback bind arguments
 */
LNSql.prototype.execute=function(file,href,href2) {
	return this.execute_helper(file,href,0,0,href2);
}

/**
 * Method: execute_and_fetch_one
 *		Executes an SQL file and returns first SELECT result row
 *
 * Parameters:
 *		file typeof String	- file File name without .sql extension
 *		href typeof Object	- Bind arguments
 *		href2 typeof Object	- href2 Fallback bind arguments
 *
 * Returns:
 *		typeof Object - first result row, null if select returns no rows
 */
LNSql.prototype.execute_and_fetch_one=function(file,href,href2)
{
	return this.execute_helper(file,href,0,1,href2);
}

/**
 * Method: execute_and_fetch
 *		Executes an SQL file and returns array of SELECT result rows
 *
 * Parameters:
 * 		file typeof String	- File name without .sql extension
 *		href typeof Object	- Bind arguments
 *		href2 typeof Object	- Fallback bind arguments
 *
 * Returns:
 *		typeof Array - array of result rows
 */
LNSql.prototype.execute_and_fetch=function(file,href,href2)
{
	return this.execute_helper(file,href,0,-1,href2);
}

/**
 * Method: execute_single
 *		Executes an SQL query (used for INSERT/UPDATE/DELETE)
 *
 * Parameters:
 *		q typeof String		- SQL query
 *		href typeof Object	- href Bind arguments
 *		href2 typeof Object	- href2 Fallback bind arguments
 */
LNSql.prototype.execute_single=function(q,href,href2)
{
	return this.execute_helper(q,href,1,0,href2);
}

/**
 * Method: execute_and_fetch_one_single
 *		Executes an SQL query and returns first SELECT result row
 *
 * Parameters:
 *		q typeof String		- SQL query
 *		href typeof Object	- href Bind arguments
 *		href2 typeof Object	- href2 Fallback bind arguments
 *
 * Returns:
 *		typeof Object - first result row, null if select returns no rows
 */
LNSql.prototype.execute_and_fetch_one_single=function(q,href,href2)
{
	return this.execute_helper(q,href,1,1,href2);
}

/**
 * Method: execute_and_fetch_single
 *		Executes an SQL query and returns array of SELECT result rows
 *
 * Parameters:
 *		q typeof String		- SQL query
 *		href typeof Object	- href Bind arguments
 *		href2 typeof Object	- href2 Fallback bind arguments
 *
 * Returns:
 *		typeof Array - array of result rows
 */
LNSql.prototype.execute_and_fetch_single=function(q,href,href2)
{
	return this.execute_helper(q,href,1,-1,href2);
}

/**
 * Method: execute_and_fetch_h
 *		Executes an SQL file recursively and returns a joined array of SELECT result rows
 *		SQL query must feature "coalesce(parent_id,0)=coalesce(:parent_id,0)" expression.
 *		Initial parent_id can be given via href argument.
 *
 * Parameters:
 * 		file typeof String	- File name without .sql extension
 * 		href typeof Object	-  Bind arguments
 *		href2 typeof Object	- Fallback bind arguments
 *
 * Results:
 * typeof Array - A joined array of result rows
 */
LNSql.prototype.execute_and_fetch_h=function(file,href,href2)
{
	if (!this.prepared_query[file]) this.prepare(this.load(file),file);
	return this.execute_and_fetch_h2(file,href,0,href2);
}

/**
 * Method: execute_and_fetch_h_single
 *		Executes an SQL query recursively and returns a joined array of SELECT result rows
 *
 * Parameters:
 *		q typeof String		- SQL query
 *		href typeof Object	- href Bind arguments
 *		href2 typeof Object	- href2 Fallback bind arguments
 *
 * Returns:
 *		typeof Array - array of result rows
 */
LNSql.prototype.execute_and_fetch_h_single=function(q,href,href2)
{
	if (!this.prepared_query[q]) this.prepare(q,file);
	return this.execute_and_fetch_h2(q,href,0,href2);
}

/*****************************************************************************/
LNSql.prototype.execute_and_fetch_ha=function(file,href,fields)
{
	if (!this.prepared_query[file]) this.prepare(this.load(file),file);
	return this.execute_and_fetch_h2(file,href,1,fields);
}

/*****************************************************************************/
LNSql.prototype.execute_and_fetch_ha_single=function(file,href,fields)
{
	if (!this.prepared_query[file]) this.prepare(file,file);
	return this.execute_and_fetch_h2(file,href,1,fields);
}


/*****************************************************************************/
LNSql.prototype.execute_and_fetch_h2=function(file,href,use_array,fields)
{
	if (!href) href={};
	var parent_id_pos;
	var parr=this.prepared_array[file];
	for (var i=0;i<parr.length;i++) if (parr[i]=='parent_id') parent_id_pos=i;
	if (parent_id_pos==undefined) throw new Error("SQL:execute_and_fetch_h() - parent_id_pos==undefined");
	var values=this.bind(file,href,fields);
	var arr=[];
	this.execute_and_fetch_h3(this.prepared_query[file],values,parent_id_pos,href.parent_id||0,1,arr,use_array,fields);
	return arr;
}

/*****************************************************************************/
LNSql.prototype.execute_and_fetch_h3=function(txt,values,parent_id_pos,parent_id,level,arr,use_array,fields)
{
	values[parent_id_pos]=parent_id;
	this.auto_start_transaction();
	if (this.config.on_query) this.config.on_query(this,txt);
	var res=this.dbh.queryParams(txt,values);
//	throw("txt="+txt+", values="+values);
//	var cnt=res.numRows();
	var arr2=res.fetchAllObjects();
	for (var i=0;i<arr2.length;i++) {
		var ret=arr2[i];
		ret.level=level;
		arr.push(ret);
		if (use_array) {
			var children=[];
			this.execute_and_fetch_h3(txt,values,parent_id_pos,ret.id,level+1,children,use_array);
			if (children.length) ret.children=children;
		} else {
			this.execute_and_fetch_h3(txt,values,parent_id_pos,ret.id,level+1,arr,use_array);
		}
	}
}

/*****************************************************************************/
LNSql.prototype.logs_to_html_comment=function()
{
	if (!this.log_enabled) return "";
	function safe(a) {
		return a;//.replace(/&/g,"&amp;").replace(/</g,"&lt;").replace(/>/);
	}
	function fmt(a) {
		var s=Math.round(100*a).toString();
		if (s.length==1) return "0.0"+s;
		if (s.length==2) return "0."+s;
		var arr=s.match(/^(\d+)(\d\d)$/);
		if (!arr) return s;
		return arr[1]+"."+arr[2];
	}

	var r="<!-- SQL STATS\n";
	var total=0;
	for (var k in this.logs) {
		var a=this.logs[k];
		if (k.match(/\n/)) {
			r+="\n"+k+"\n\tcount="+a.cnt+", time="+fmt(a.time)+"\n"
		} else {
			r+=safe(k)+": count="+a.cnt+", time="+fmt(a.time)+((a.ids && a.ids.length)?", ids="+a.ids.join(","):"")+"\n";
		}
		r+="\n";
		total+=a.time;
	}
	r+="===== total SQL time: "+fmt(total)+"\n";
	r+="-->\n";
	return r;
}
/*****************************************************************************/
exports.LNSql=LNSql;

/*****************************************************************************/
